﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Collections;
using System.ComponentModel;
using CalemShop.Classes;

namespace CalemShop
{
    public class Database
    {
        public static Hashtable ItemList = new Hashtable();
        public static Hashtable ItemListNew = new Hashtable();
        public static AutoCompleteStringCollection listItemAutoKeys = new AutoCompleteStringCollection();
        public static AutoCompleteStringCollection supplierCollection = new AutoCompleteStringCollection();
        public static AutoCompleteStringCollection colorCollection = new AutoCompleteStringCollection();
        public static AutoCompleteStringCollection itemNameCollection = new AutoCompleteStringCollection();
        public static bool authenticate(string acc, string pass)
        {
            MySqlConnection conn = DBConnection.getConnection();
            
            try
            {
                MySqlCommand command = new MySqlCommand("select uid, permission from sys_user where account =? and pass=?");
                command.Parameters.AddWithValue("p1", acc);
                command.Parameters.AddWithValue("p2", pass);
                command.Connection = conn;
                conn.Open();
                MySqlDataReader rs = command.ExecuteReader();
                if (rs.HasRows)
                {
                    rs.Close();
                    return true;
                }

                rs.Close();
                return false;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }
        private static int maxItemInStock = -1;
        private static Dictionary<string, Customer> customerCache = new Dictionary<string, Customer>();
        private static Dictionary<string, Staff> staffCache = new Dictionary<string, Staff>();

        public static DataTable getStock(int page)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                int start = page - 1;
                if (start < 0)
                {
                    start = 0;
                }

                start = start * 20;
                if (start > maxItemInStock && maxItemInStock > 0)
                {
                    start = 0;
                }
                DataTable table = new DataTable();
                MySqlDataAdapter adap = new MySqlDataAdapter("select * from shop_stock, item_type where `type` = `typeId` limit " + start + ", 20", conn);
                adap.Fill(table);
                return table;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static DataTable getStocWithItemEmpty()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable table = new DataTable();
                MySqlDataAdapter adap = new MySqlDataAdapter("select * from shop_stock, item_type where `type` = `typeId` and amount <= 0", conn);
                adap.Fill(table);
                return table;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static ItemEntity getStockItem(string itemId)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand("select * from shop_stock, item_type where `type` = `typeId` and `itemId`=?", conn);
                selectCommand.Parameters.AddWithValue("itemId", itemId);
                MySqlDataReader rs = selectCommand.ExecuteReader();
                ItemEntity item = null;
                if (rs.Read())
                {
                    item = new ItemEntity();
                    item.id = rs.GetString("itemId");
                    item.name = rs.GetString("itemName");
                    item.desc = rs.GetString("desc");
                    item.color = rs.GetString("color");
                    item.size = rs.GetString("size");
                    item.supplier = rs.GetString("supplier");
                    item.gender = rs.GetString("gender");
                    item.typeName = rs.GetString("typeName");
                    item.cost = rs.GetInt32("cost");
                    item.price = rs.GetInt32("price");
                    item.giasi = rs.GetInt32("giasi");
                    item.amount = rs.GetInt32("amount");
                    item.autoKey = rs.GetInt32("autoKey");
                    //item.type = rs.GetInt32("typeId");
                }

                rs.Close();
                selectCommand.Dispose();
                return item;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }
        }

        public static ItemEntity getStockItem(int autoKey)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand("select * from shop_stock, item_type where `type` = `typeId` and `shop_stock`.`autoKey`=?", conn);
                selectCommand.Parameters.AddWithValue("autoKey", autoKey);
                MySqlDataReader rs = selectCommand.ExecuteReader();
                ItemEntity item = null;
                if (rs.Read())
                {
                    item = new ItemEntity();
                    item.id = rs.GetString("itemId");
                    item.name = rs.GetString("itemName");
                    item.desc = rs.GetString("desc");
                    item.color = rs.GetString("color");
                    item.size = rs.GetString("size");
                    item.supplier = rs.GetString("supplier");
                    item.gender = rs.GetString("gender");
                    item.typeName = rs.GetString("typeName");
                    item.cost = rs.GetInt32("cost");
                    item.price = rs.GetInt32("price");
                    item.giasi = rs.GetInt32("giasi");
                    item.amount = rs.GetInt32("amount");
                    item.autoKey = rs.GetInt32("autoKey");
                    item.type = rs.GetInt32("typeId");
                }

                rs.Close();
                selectCommand.Dispose();
                return item;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }
        }

        public static Hashtable getStockItemPrice(int fromId, int toId)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                Hashtable map = new Hashtable();
                conn.Open();
                MySqlCommand cmd = new MySqlCommand("select autoKey,price from shop_stock, item_type where autoKey >=" + fromId + " and autoKey <=" + toId, conn);
                MySqlDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    map[rs.GetInt32("autoKey").ToString()] = rs.GetInt32("price");
                }

                rs.Close();
                return map;

            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }


        public static Hashtable getConfigs()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand("select * config", conn);
                MySqlDataReader rs = selectCommand.ExecuteReader();
                Hashtable config = new Hashtable();
                if (rs.Read())
                {
                    config.Add(rs.GetString("configKey"), rs.GetString(rs.GetString("value")));
                }

                rs.Close();
                selectCommand.Dispose();
                return config;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }
        }

        public static long getLastItemIdInStock()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand("select max(autoKey) as lastId from shop_stock;", conn);
                string snum = selectCommand.ExecuteScalar().ToString();
                if (string.IsNullOrEmpty(snum))
                {
                    selectCommand.Dispose();
                    return 0;
                }
                long id = long.Parse(snum);
                selectCommand.Dispose();
                return id;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }
        }

        public static DataTable searchItemInStock(string itemId)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                DataTable table = new DataTable();
                MySqlCommand selectCommand = new MySqlCommand("select * from shop_stock, item_type where `type` = `typeId` and `itemId`=?", conn);
                selectCommand.Parameters.AddWithValue("itemId", itemId);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(table);
                return table;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static DataTable searchItemInStockByAutoKey(string autoKey)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                DataTable table = new DataTable();
                MySqlCommand selectCommand = new MySqlCommand("select * from shop_stock, item_type where `type` = `typeId` and `autoKey`=?", conn);
                selectCommand.Parameters.AddWithValue("autoKey", autoKey);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(table);
                return table;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static DataTable searchItemInStockByName(string name)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                DataTable table = new DataTable();
                name = name.Replace("'", "");
                MySqlCommand selectCommand = new MySqlCommand("select * from shop_stock, item_type where `type` = `typeId` and `itemName` like '%" + name + "%' limit 100", conn);
                selectCommand.Parameters.AddWithValue("itemName", name);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(table);
                return table;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static decimal totalCostInStock()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                string sql = "select sum(cost * amount) as total FROM shop_stock WHERE amount > 0";
                decimal income = 0;
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                income = (decimal)selectCommand.ExecuteScalar();
                return income;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return 0;

        }

        public static decimal totalIncomeToday()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                string day = DateTime.Now.ToString("yyyy-MM-dd");
                string sql = "select distinct SUM(r.total) as income from invoice i,invoicerow r where i.invoiceId = r.invoiceId and DATE(i.invoiceDate) = ?";
                decimal income = 0;
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("sTime", day);
                income = (decimal)selectCommand.ExecuteScalar();
                return income;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return 0;

        }

        public static decimal totalIncome(string from, string to)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                string sql = "select SUM(r.total) as income from invoice i,invoicerow r where i.invoiceId = r.invoiceId and DATE(i.invoiceDate) >= ? and DATE(i.invoiceDate) <= ?";
                decimal income = 0;
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("sTime", from);
                selectCommand.Parameters.AddWithValue("eTime", to);
                income = (decimal)selectCommand.ExecuteScalar();
                return income;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return 0;

        }

        public static decimal totalSaleOff(string from, string to)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                string sql = "select SUM(s.realValue) as saleOffMoney from invoice i,sale_off_balance s where i.invoiceId = s.invoiceId and DATE(i.invoiceDate) >= ? and DATE(i.invoiceDate) <= ?";
                decimal income = 0;
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("sTime", from);
                selectCommand.Parameters.AddWithValue("eTime", to);
                income = (decimal)selectCommand.ExecuteScalar();
                return income;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return 0;

        }

        public static decimal countInvoiceReturnToday()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                string day = DateTime.Now.ToString("yyyy-MM-dd");
                string sql = "select count(*) as income from invoice where DATE(invoiceDate) = ? and invoiceType = 1";
                long count = 0;
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("sTime", day);
                count = (long)selectCommand.ExecuteScalar();
                return count;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return 0;

        }

        public static decimal countInvoiceReturn(string from, string to)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                //string sql = "select count(r.autoKey) as income from invoice i,invoicerow r where i.invoiceId = r.invoiceId and DATE(i.invoiceDate) >= ? and DATE(i.invoiceDate) <= ? and i.invoiceType = 1";
                string sql = "select count(*) as income from invoice where DATE(invoiceDate) >= ? and DATE(invoiceDate) <= ? and invoiceType = 1";
                long count = 0;
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("sTime", from);
                selectCommand.Parameters.AddWithValue("eTime", to);
                count = (long)selectCommand.ExecuteScalar();
                return count;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return 0;

        }

        public static decimal countInvoice(string from, string to)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                string sql = "select count(invoiceId) as totalInvoice from invoice where DATE(invoiceDate) >= ? and DATE(invoiceDate) <= ? and invoiceType = 0";
                long count = 0;
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("sTime", from);
                selectCommand.Parameters.AddWithValue("eTime", to);
                count = (long)selectCommand.ExecuteScalar();
                selectCommand.Dispose();
                return count;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return 0;

        }

        public static DataTable reportIncome(string from, string to)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "select DATE(invoiceDate) as dateIncome, sum(total) as income from invoice i, invoicerow r where i.invoiceId = r.invoiceId and DATE(invoiceDate) >= ? and DATE(invoiceDate) <= ? GROUP BY DATE(i.invoiceDate);";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("sTime", from);
                selectCommand.Parameters.AddWithValue("eTime", to);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(report);
                adap.Dispose();
                selectCommand.Dispose();
                return report;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static DataTable reportSellers(string from, string to)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "select i.seller, count(i.seller) as selling, sum(r.total) as sellerIncome from invoice i, invoicerow r where i.invoiceId = r.invoiceId and DATE(i.invoiceDate) >= ? and DATE(i.invoiceDate) <= ? GROUP BY seller;";
                //"select seller, count(seller) as selling, sum(total) as sellerIncome from invoice where DATE(invoiceDate) >= ? and DATE(invoiceDate) <= ? GROUP BY seller;";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("sTime", from);
                selectCommand.Parameters.AddWithValue("eTime", to);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(report);
                adap.Dispose();
                selectCommand.Dispose();
                return report;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static DataTable getInvoiceIds(string frmDay, string toDay)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                conn.Open();
                DataTable table = new DataTable();
                string sql = "select invoiceId from invoice where DATE(invoiceDate) >= ? and DATE(invoiceDate) <=? order by invoiceId desc;";
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                selectCommand.Parameters.AddWithValue("sTime", frmDay);
                selectCommand.Parameters.AddWithValue("eTime", toDay);
                adap.Fill(table);
                adap.Dispose();
                return table;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static DataTable getInvoiceBuying(int invoiceId)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable table = new DataTable();
                MySqlDataAdapter adap = new MySqlDataAdapter("select * from invoice i, invoiceRow r where i.invoiceId= r.invoiceId and i.invoiceId =" + invoiceId, conn);
                adap.Fill(table);
                adap.Dispose();
                return table;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static long getInvoiceBuyingTotalMoney(int invoiceId)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                conn.Open();
                //MySqlCommand selectCmd = new MySqlCommand("select sum(r.total) as totalMoney from invoice i, invoiceRow r where i.invoiceId= r.invoiceId and i.invoiceId =" + invoiceId + " and invoiceType = 0", conn);
                MySqlCommand selectCmd = new MySqlCommand("select sum(r.total) as totalMoney from invoice i, invoiceRow r where i.invoiceId= r.invoiceId and i.invoiceId =" + invoiceId, conn);
                long money = long.Parse(selectCmd.ExecuteScalar().ToString());
                selectCmd.Dispose();
                return money;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return 0;

        }

        public static DataTable getItemType()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable table = new DataTable();
                MySqlDataAdapter adap = new MySqlDataAdapter("select * from item_type", conn);
                adap.Fill(table);
                return table;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static void saveStock(DataTable table)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                MySqlDataAdapter adap = new MySqlDataAdapter("select * from shop_stock", conn);
                MySqlCommandBuilder builder = new MySqlCommandBuilder(adap);
                adap.Update(table);
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static void saveStockRows(DataRow row)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                conn.Open();
                MySqlCommand UpdateCommand = new MySqlCommand("update shop_stock set itemId=?,itemName =?,`desc`=?, `color` = ?, `gender` = ?, `price` = ?, `cost` = ?, `giasi` = ?, `amount` = ?, `type`=?, `size` = ? where autoKey = ?", conn);
                string itemId = row["ItemId"].ToString();
                UpdateCommand.Parameters.AddWithValue("itemId", row["itemId"].ToString());
                UpdateCommand.Parameters.AddWithValue("itemName", row["itemName"].ToString());
                UpdateCommand.Parameters.AddWithValue("desc", row["desc"].ToString());
                UpdateCommand.Parameters.AddWithValue("color", row["color"].ToString()); // string
                UpdateCommand.Parameters.AddWithValue("gender", row["gender"].ToString()); // string
                UpdateCommand.Parameters.AddWithValue("price", row["price"].ToString());
                UpdateCommand.Parameters.AddWithValue("cost", row["cost"].ToString()); // int
                UpdateCommand.Parameters.AddWithValue("giasi", row["giasi"].ToString()); // int
                UpdateCommand.Parameters.AddWithValue("amount", row["amount"].ToString());
                UpdateCommand.Parameters.AddWithValue("type", row["type"].ToString());
                UpdateCommand.Parameters.AddWithValue("size", row["size"].ToString());
                UpdateCommand.Parameters.AddWithValue("autokey", row["autoKey"].ToString());
                UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }


        public static void deleteStockRow(DataRow row)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                conn.Open();
                MySqlCommand deleteCmd = new MySqlCommand("delete from shop_stock where autoKey = ?", conn);
                string itemId = row["ItemId"].ToString();
                deleteCmd.Parameters.AddWithValue("autokey", row["autoKey"].ToString());
                deleteCmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static void deleteStockRowByItemId(DataRow row)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                conn.Open();
                MySqlCommand deleteCmd = new MySqlCommand("delete from shop_stock where itemId = ?", conn);
                deleteCmd.Parameters.AddWithValue("itemId", row["itemId"].ToString());
                deleteCmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static void addStockRows(DataRow row)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                conn.Open();

                MySqlCommand UpdateCommand = new MySqlCommand("insert ignore into shop_stock(`itemId`, `itemName`, `desc`, `color`, `gender`, `size`, `amount`, `price`, `cost`, `giasi`, `type`) values(?,?,?,?,?,?,?,?,?,?,?);", conn);
                UpdateCommand.Parameters.AddWithValue("itemId", row["itemId"].ToString());
                UpdateCommand.Parameters.AddWithValue("itemName", row["itemName"].ToString());
                UpdateCommand.Parameters.AddWithValue("desc", row["desc"].ToString());
                UpdateCommand.Parameters.AddWithValue("color", row["color"].ToString());
                UpdateCommand.Parameters.AddWithValue("gender", row["gender"].ToString());
                UpdateCommand.Parameters.AddWithValue("size", row["size"].ToString());

                UpdateCommand.Parameters.AddWithValue("amount", Int32.Parse(row["amount"].ToString()));
                UpdateCommand.Parameters.AddWithValue("price", Int32.Parse(row["price"].ToString()));
                UpdateCommand.Parameters.AddWithValue("cost", Int32.Parse(row["cost"].ToString()));
                UpdateCommand.Parameters.AddWithValue("giasi", Int32.Parse(row["giasi"].ToString()));
                UpdateCommand.Parameters.AddWithValue("type", Int32.Parse(row["type"].ToString()));
                UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static void loadSupplierAndColorCollection()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                conn.Open();
                MySqlCommand cmd = new MySqlCommand("select supplier, color from shop_stock", conn);
                MySqlDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    supplierCollection.Add(rs.GetString("supplier"));
                    colorCollection.Add(rs.GetString("color"));
                }

                rs.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static AutoCompleteStringCollection getAllItemIds()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                conn.Open();
                MySqlCommand cmd = new MySqlCommand("select autoKey, itemId, itemName, cost, price, giasi, amount from shop_stock", conn);
                MySqlDataReader rs = cmd.ExecuteReader();
                AutoCompleteStringCollection list = new AutoCompleteStringCollection();
                itemNameCollection.Clear();
                listItemAutoKeys.Clear();
                ItemList.Clear();
                ItemListNew.Clear();
                maxItemInStock = 0;
                while (rs.Read())
                {
                    list.Add(rs.GetString("itemId"));
                    listItemAutoKeys.Add(rs.GetString("autoKey"));
                    itemNameCollection.Add(rs.GetString("itemName"));
                    ItemEntity item = new ItemEntity(rs.GetString("itemId"), rs.GetString("itemName"), rs.GetInt32("price"));
                    item.cost = rs.GetInt32("cost");
                    item.giasi = rs.GetInt32("giasi");
                    item.amount = rs.GetInt32("amount");
                    item.autoKey = rs.GetInt32("autoKey");

                    ItemList.Add(rs.GetString("itemId"), item);
                    ItemListNew.Add(rs.GetString("autoKey"), item);
                    maxItemInStock++;
                }

                rs.Close();
                return list;

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return new AutoCompleteStringCollection();
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static AutoCompleteStringCollection getAllInvoiceIds()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                conn.Open();
                MySqlCommand cmd = new MySqlCommand("select invoiceId from invoice", conn);
                MySqlDataReader rs = cmd.ExecuteReader();
                AutoCompleteStringCollection list = new AutoCompleteStringCollection();
                while (rs.Read())
                {
                    list.Add(rs.GetString("invoiceId"));
                }

                rs.Close();
                return list;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return new AutoCompleteStringCollection();
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static AutoCompleteStringCollection getInvoiceIds()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                conn.Open();
                MySqlCommand cmd = new MySqlCommand("select invoiceId from invoice", conn);
                MySqlDataReader rs = cmd.ExecuteReader();
                AutoCompleteStringCollection list = new AutoCompleteStringCollection();
                while (rs.Read())
                {
                    list.Add(rs.GetString("invoiceId"));
                }

                rs.Close();
                return list;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return new AutoCompleteStringCollection();
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static Staff getUserByName(string name)
        {
            return staffCache[name];
        }

        public static Staff getUserBySerial(string serial)
        {
            if (staffCache.Count > 0)
            {
                foreach (Staff u in staffCache.Values)
                {
                    if (string.Compare(serial, u.serial, true) == 0)
                    {
                        return u;
                    }
                }
            }

            return null;
        }


        public static ArrayList getUserList()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {

                conn.Open();
                MySqlCommand cmd = new MySqlCommand("select `account`, `serial`, `permission` from sys_user", conn);
                MySqlDataReader rs = cmd.ExecuteReader();
                ArrayList list = new ArrayList();
                staffCache.Clear();
                while (rs.Read())
                {
                    list.Add(rs.GetString("account"));
                    Staff user = new Staff(rs.GetString("account"), rs.GetString("serial"), rs.GetInt32("permission"));
                    staffCache[user.name] = user;
                }

                rs.Close();
                return list;

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return new ArrayList();
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static ItemEntity getItemInStock(String itemId)
        {
            return (ItemEntity)ItemList[itemId];
        }

        public static ItemEntity getItemInStockByAutoKey(String autoKey)
        {
            return (ItemEntity)ItemListNew[autoKey];
        }

        public static long createInvoiceBuying(string createDate, string seller, string customer, ArrayList rows, decimal percent)
        {
            MySqlConnection conn = DBConnection.getConnection();
            MySqlTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                MySqlCommand inserCmd = new MySqlCommand("insert into invoice(`invoiceType`,`discount`, `seller`, `customer`, `invoiceDate`) values(0,?,?,?,?)", conn);
                inserCmd.Parameters.AddWithValue("discount", percent);
                inserCmd.Parameters.AddWithValue("seller", seller);
                inserCmd.Parameters.AddWithValue("customer", customer);
                inserCmd.Parameters.AddWithValue("invoiceDate", createDate);
                inserCmd.ExecuteNonQuery();
                long invoiceId = inserCmd.LastInsertedId;
                if (invoiceId > 0)
                {
                    int count = 0;
                    foreach (InvoiceRowEntity item in rows)
                    {
                        int executeInsert = 0;
                        int executeUpdate = 0;
                        MySqlCommand insertRowCmd = new MySqlCommand("insert into invoiceRow(`invoiceId`, `price`,`ck`, `amount`, `total`,`itemId`, `itemName`) values(?,?,?,?,?,?,?)", conn);
                        insertRowCmd.Parameters.AddWithValue("invoiceId", invoiceId); // int
                        insertRowCmd.Parameters.AddWithValue("price", item.price); // int
                        insertRowCmd.Parameters.AddWithValue("ck", item.ck); // int
                        insertRowCmd.Parameters.AddWithValue("amount", item.amount); // int
                        if (item.ck > 0)
                        {
                            decimal t = item.total - Math.Round((item.total * item.ck / 100));
                            insertRowCmd.Parameters.AddWithValue("total", t); // int
                        }
                        else
                        {
                            insertRowCmd.Parameters.AddWithValue("total", item.total); // int
                        }

                        //insertRowCmd.Parameters.AddWithValue("itemId", item.itemId); // string
                        insertRowCmd.Parameters.AddWithValue("itemId", item.autoKey.ToString()); // string
                        insertRowCmd.Parameters.AddWithValue("itemName", item.itemName); // string
                        executeInsert = insertRowCmd.ExecuteNonQuery();

                        MySqlCommand updateRowCmd = new MySqlCommand("update shop_stock set amount = amount - " + item.amount + "  where itemId = ?", conn);
                        updateRowCmd.Parameters.AddWithValue("itemId", item.itemId); // string
                        executeUpdate = updateRowCmd.ExecuteNonQuery();

                        insertRowCmd.Dispose();
                        updateRowCmd.Dispose();
                        if (executeInsert > 0 && executeUpdate > 0)
                        {
                            count++;
                        }
                    }



                    if (count == rows.Count)
                    {
                        trans.Commit();


                        return invoiceId;
                    }
                    else
                    {
                        trans.Rollback();
                    }
                }
                else
                {

                    trans.Rollback();
                }

                return 0;
            }
            catch (Exception e)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                }
                MessageBox.Show(e.Message);
                return 0;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }


        public static long createInvoiceReturning(string seller, string customer, string reason, ArrayList rows)
        {
            MySqlConnection conn = DBConnection.getConnection();
            MySqlTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                MySqlCommand inserCmd = new MySqlCommand("insert into invoice(`invoiceType`, `seller`, `customer`,`note`) values(1,?,?,?)", conn);
                inserCmd.Parameters.AddWithValue("seller", seller);
                inserCmd.Parameters.AddWithValue("customer", customer);
                inserCmd.Parameters.AddWithValue("note", reason);
                inserCmd.ExecuteNonQuery();
                long invoiceId = inserCmd.LastInsertedId;
                if (invoiceId > 0)
                {
                    int count = 0;
                    foreach (InvoiceRowEntity item in rows)
                    {
                        int executeInsert = 0;
                        int executeUpdate = 0;
                        MySqlCommand insertRowCmd = new MySqlCommand("insert into invoiceRow(`invoiceId`, `price`, `ck`, `amount`, `total`,`itemId`, `itemName`) values(?,?,?,?,?,?,?)", conn);
                        insertRowCmd.Parameters.AddWithValue("invoiceId", invoiceId); // int
                        insertRowCmd.Parameters.AddWithValue("price", -item.price); // int
                        insertRowCmd.Parameters.AddWithValue("ck", item.ck); // int
                        insertRowCmd.Parameters.AddWithValue("amount", item.amount); // int
                        if (item.ck > 0)
                        {
                            decimal t = item.total - Math.Round((item.total * item.ck / 100));
                            insertRowCmd.Parameters.AddWithValue("total", -t); // int
                        }
                        else
                        {
                            insertRowCmd.Parameters.AddWithValue("total", -item.total); // int
                        }

                        //insertRowCmd.Parameters.AddWithValue("itemId", item.itemId); // string
                        insertRowCmd.Parameters.AddWithValue("itemId", item.autoKey.ToString()); // string
                        insertRowCmd.Parameters.AddWithValue("itemName", item.itemName); // string
                        executeInsert = insertRowCmd.ExecuteNonQuery();

                        MySqlCommand updateRowCmd = new MySqlCommand("update shop_stock set amount = amount + " + item.amount + "  where itemId = ?", conn);
                        updateRowCmd.Parameters.AddWithValue("itemId", item.itemId); // string
                        executeUpdate = updateRowCmd.ExecuteNonQuery();

                        insertRowCmd.Dispose();
                        updateRowCmd.Dispose();
                        if (executeInsert > 0 && executeUpdate > 0)
                        {
                            count++;
                        }
                    }

                    if (count == rows.Count)
                    {
                        trans.Commit();


                        return invoiceId;
                    }
                    else
                    {
                        trans.Rollback();
                    }
                }
                else
                {

                    trans.Rollback();
                }

                return 0;
            }
            catch (Exception e)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                }
                MessageBox.Show(e.Message);
                return 0;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static long createInvoiceOutcoming(string seller, string customer, string reason, decimal money)
        {
            MySqlConnection conn = DBConnection.getConnection();
            MySqlTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                MySqlCommand inserCmd = new MySqlCommand("insert into invoice(`invoiceType`, `seller`, `customer`,`note`) values(2,?,?,?)", conn, trans);
                inserCmd.Parameters.AddWithValue("seller", seller);
                inserCmd.Parameters.AddWithValue("customer", customer);
                inserCmd.Parameters.AddWithValue("note", reason);
                inserCmd.ExecuteNonQuery();
                long invoiceId = inserCmd.LastInsertedId;
                if (invoiceId > 0)
                {
                    MySqlCommand insertRowCmd = new MySqlCommand("insert into invoiceRow(`invoiceId`, `price`, `ck`, `amount`, `total`,`itemId`, `itemName`) values(?,?,?,?,?,?,?)", conn, trans);
                    insertRowCmd.Parameters.AddWithValue("invoiceId", invoiceId); // int
                    insertRowCmd.Parameters.AddWithValue("price", -money); // int
                    insertRowCmd.Parameters.AddWithValue("ck", 0); // int
                    insertRowCmd.Parameters.AddWithValue("amount", 1); // int
                    insertRowCmd.Parameters.AddWithValue("total", -money); // int
                    insertRowCmd.Parameters.AddWithValue("itemId", "SPI001"); // string
                    insertRowCmd.Parameters.AddWithValue("itemName", "SPI001"); // string
                    if (insertRowCmd.ExecuteNonQuery() > 0)
                    {
                        trans.Commit();
                        insertRowCmd.Dispose();
                        return invoiceId;
                    }
                    else
                    {
                        trans.Rollback();
                        insertRowCmd.Dispose();
                    }
                }
                else
                {
                    trans.Rollback();
                }

                return 0;
            }
            catch (Exception e)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                }
                MessageBox.Show(e.Message);
                return 0;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static int executeQuerys(ArrayList sqlList, BackgroundWorker bw)
        {
            if (sqlList.Count == 0)
            {
                return 0;
            }

            MySqlConnection conn = DBConnection.getConnection();
            MySqlTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                int totalCount = 0;
                int count = 0;
                bool needCommit = false;
                foreach (string sql in sqlList)
                {
                    if (string.IsNullOrEmpty(sql))
                    {
                        continue;
                    }

                    MySqlCommand inserCmd = new MySqlCommand(sql, conn, trans);
                    needCommit = true;
                    if (inserCmd.ExecuteNonQuery() > 0)
                    {
                        totalCount++;
                    }
                    count++;
                    inserCmd.Dispose();
                    if (count > 0 && count == 3)
                    {
                        count = 0;
                        trans.Commit();
                        trans = conn.BeginTransaction();
                    }

                    if (bw != null)
                    {
                        bw.ReportProgress(totalCount);
                    }
                }

                if (needCommit)
                {
                    trans.Commit();
                }
                return totalCount;
            }
            catch (Exception e)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                }
                MessageBox.Show(e.Message);
                return -1;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static int executeQuerys(params string[] sqlList)
        {
            if (sqlList.Length == 0)
            {
                return 0;
            }

            MySqlConnection conn = DBConnection.getConnection();
            MySqlTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                int totalCount = 0;
                int count = 0;
                bool needCommit = false;
                foreach (string sql in sqlList)
                {
                    if (string.IsNullOrEmpty(sql))
                    {
                        continue;
                    }

                    MySqlCommand inserCmd = new MySqlCommand(sql, conn, trans);
                    needCommit = true;
                    if (inserCmd.ExecuteNonQuery() > 0)
                    {
                        totalCount++;
                    }
                    count++;
                    inserCmd.Dispose();
                    if (count > 0 && count == 3)
                    {
                        count = 0;
                        trans.Commit();
                        trans = conn.BeginTransaction();
                    }
                }

                if (needCommit)
                {
                    trans.Commit();
                }
                return totalCount;
            }
            catch (Exception e)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                }
                MessageBox.Show(e.Message);
                return 0;
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static DataTable getStockByType(int type)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "select * from shop_stock where `type` = ? order by itemId asc";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("type", type);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(report);
                adap.Dispose();
                selectCommand.Dispose();
                return report;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static DataTable getStockBy(string typeFilter)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "select * from shop_stock " + typeFilter + " order by autoKey asc";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(report);
                adap.Dispose();
                selectCommand.Dispose();
                return report;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static long insertCustomer(Customer cus)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "insert ignore into customer(customerName,serial, phone, email, facebook, createTime) values(?,?,?,?,?, NOW())";
                conn.Open();
                long autoId = 0;
                MySqlCommand insertCommand = new MySqlCommand(sql, conn);
                insertCommand.Parameters.AddWithValue("customerName", cus.customerName);
                insertCommand.Parameters.AddWithValue("serial", cus.serial);
                insertCommand.Parameters.AddWithValue("phone", cus.phone);
                insertCommand.Parameters.AddWithValue("email", cus.email);
                insertCommand.Parameters.AddWithValue("facebook", cus.facebook);
                insertCommand.ExecuteNonQuery();
                autoId = insertCommand.LastInsertedId;
                insertCommand.Dispose();
                return autoId;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return 0;

        }

        public static void insertCustomers(ArrayList cuslist)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "insert ignore into customer(customerName,serial, phone, email, facebook, point, createTime) values(?,?,?,?,?,?, NOW())";
                conn.Open();
                MySqlTransaction trans = conn.BeginTransaction();
                MySqlCommand insertCommand = new MySqlCommand(sql, conn, trans);
                foreach (Customer cus in cuslist)
                {
                    insertCommand.Parameters.AddWithValue("customerName", cus.customerName);
                    insertCommand.Parameters.AddWithValue("serial", cus.serial);
                    insertCommand.Parameters.AddWithValue("phone", cus.phone);
                    insertCommand.Parameters.AddWithValue("email", cus.email);
                    insertCommand.Parameters.AddWithValue("facebook", cus.facebook);
                    insertCommand.Parameters.AddWithValue("point", 0);
                    insertCommand.ExecuteNonQuery();
                }

                trans.Commit();
                insertCommand.Dispose();

            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

        }

        public static AutoCompleteStringCollection getCustomerDataCollection()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                AutoCompleteStringCollection report = new AutoCompleteStringCollection();
                string sql = "select * from customer";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                MySqlDataReader rs = selectCommand.ExecuteReader();
                while (rs.Read())
                {
                    try
                    {
                        report.Add(rs.GetString("customerName"));
                        report.Add(rs.GetString("phone"));
                        report.Add(rs.GetString("serial"));
                    }
                    catch (Exception)
                    {
                        continue;
                    }
                }

                rs.Close();
                selectCommand.Dispose();
                return report;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static DataTable searchCustomer(string value)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                //string sql = "select c.customerId, c.customerName, c.serial, c.phone, c.email, c.facebook, c.createTime, sum(t.money) as totalPaid from customer c, customer_trade t where c.customerId = t.customerId and (c.serial = ? or c.phone = ? or c.customerName like ?) group by t.customerId";

                string sql = "select c.customerId, c.customerName, c.serial, c.phone, c.email, c.facebook, c.createTime, sum(t.money) as totalPaid from customer c left join customer_trade as t on c.customerId = t.customerId group by c.customerId having c.serial = ? or c.phone like ? or c.customerName like ?";

                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("serial", value);
                selectCommand.Parameters.AddWithValue("phone", "%" + value + "%");
                selectCommand.Parameters.AddWithValue("customerName", "%" + value + "%");
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(report);
                adap.Dispose();
                selectCommand.Dispose();
                return report;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static DataTable getAllCustomer()
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                //string sql = "select c.customerId, c.customerName, c.serial, c.phone, c.email, c.facebook, c.createTime, sum(t.money) as totalPaid from customer c, customer_trade t where c.customerId = t.customerId group by t.customerId";
                string sql = "select c.customerId, c.customerName, c.serial, c.phone, c.email, c.facebook, c.createTime, sum(t.money) as totalPaid from customer c left join customer_trade as t on c.customerId = t.customerId group by c.customerId";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                MySqlDataAdapter adap = new MySqlDataAdapter(selectCommand);
                adap.Fill(report);
                adap.Dispose();
                selectCommand.Dispose();
                return report;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static bool deleteCustomer(int customerId)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "delete from customer where customerId = ?";
                conn.Open();
                MySqlCommand deleteCommand = new MySqlCommand(sql, conn);
                deleteCommand.Parameters.AddWithValue("customerId", customerId);
                deleteCommand.ExecuteNonQuery();
                deleteCommand.Dispose();
                return true;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return false;

        }

        public static bool updateCustomer(Customer cs)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "update customer set customerName=?, phone = ?, email = ?, facebook = ?,serial=? where customerId=?";
                conn.Open();
                MySqlCommand updateCommand = new MySqlCommand(sql, conn);
                updateCommand.Parameters.AddWithValue("customerName", cs.customerName);
                updateCommand.Parameters.AddWithValue("phone", cs.phone);
                updateCommand.Parameters.AddWithValue("email", cs.email);
                updateCommand.Parameters.AddWithValue("facebook", cs.facebook);
                updateCommand.Parameters.AddWithValue("serial", cs.serial);
                updateCommand.Parameters.AddWithValue("customerId", cs.customerId);
                updateCommand.ExecuteNonQuery();
                updateCommand.Dispose();
                return true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return false;

        }

        public static bool updatePoint4Customer(Customer cs, int invoiceId, int paidMoney)
        {
            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                DataTable report = new DataTable();
                string sql = "insert ignore into customer_trade(customerId, invoiceId, money, phone) values(?,?,?,?)";
                conn.Open();
                MySqlCommand updateCommand = new MySqlCommand(sql, conn);
                updateCommand.Parameters.AddWithValue("customerId", cs.customerId);
                updateCommand.Parameters.AddWithValue("invoiceId", invoiceId);
                updateCommand.Parameters.AddWithValue("money", paidMoney);
                updateCommand.Parameters.AddWithValue("phone", cs.phone);
                int d = updateCommand.ExecuteNonQuery();
                updateCommand.Dispose();
                if (d > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return false;

        }

        public static Customer getCustomerBySerial(string serial)
        {
            if (string.IsNullOrEmpty(serial))
            {
                return null;
            }

            if (customerCache.ContainsKey(serial))
            {
                return customerCache[serial];
            }

            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                Customer cs = null;
                string sql = "select * from customer where serial = ?";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("serial", serial);
                MySqlDataReader rs = selectCommand.ExecuteReader();
                if (rs.Read())
                {
                    cs = new Customer();
                    cs.customerId = rs.GetInt32("customerId");
                    cs.customerName = rs.GetString("customerName");
                    cs.serial = rs.GetString("serial");
                    cs.phone = rs.GetString("phone");
                    try
                    {
                        cs.email = rs.GetString("email");
                        cs.facebook = rs.GetString("facebook");
                    }
                    catch (System.Data.SqlTypes.SqlNullValueException se)
                    {
                    }
                    customerCache[serial] = cs;

                }
                rs.Close();
                selectCommand.Dispose();
                return cs;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static Customer getCustomerByPhone(string phone)
        {
            if (string.IsNullOrEmpty(phone))
            {
                return null;
            }

            if (customerCache.ContainsKey(phone))
            {
                return customerCache[phone];
            }

            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                Customer cs = null;
                string sql = "select * from customer where phone = ?";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("phone", phone);
                MySqlDataReader rs = selectCommand.ExecuteReader();
                if (rs.Read())
                {
                    cs = new Customer();
                    cs.customerId = rs.GetInt32("customerId");
                    cs.customerName = rs.GetString("customerName");
                    cs.serial = rs.GetString("serial");
                    cs.phone = rs.GetString("phone");
                    cs.email = rs.GetString("email");
                    cs.facebook = rs.GetString("facebook");
                    customerCache[phone] = cs;

                }
                rs.Close();
                selectCommand.Dispose();
                return cs;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static Customer getCustomerByPhoneOrName(string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                return null;
            }

            if (customerCache.ContainsKey(value))
            {
                return customerCache[value];
            }

            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                Customer cs = null;
                string sql = "select * from customer where phone = ? or customerName = ?";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("phone", value);
                selectCommand.Parameters.AddWithValue("name", value);
                MySqlDataReader rs = selectCommand.ExecuteReader();
                if (rs.Read())
                {
                    cs = new Customer();
                    cs.customerId = rs.GetInt32("customerId");
                    cs.customerName = rs.GetString("customerName");
                    cs.serial = rs.GetString("serial");
                    cs.phone = rs.GetString("phone");
                    cs.email = rs.GetString("email");
                    cs.facebook = rs.GetString("facebook");
                    customerCache[value] = cs;

                }
                rs.Close();
                selectCommand.Dispose();
                return cs;
            }
            catch (Exception e)
            {
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }

        public static ArrayList getCustomerListByPhoneOrName(string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                return null;
            }

            MySqlConnection conn = DBConnection.getConnection();
            try
            {
                Customer cs = null;
                string sql = "select * from customer where phone like ? or customerName like ?";
                conn.Open();
                MySqlCommand selectCommand = new MySqlCommand(sql, conn);
                selectCommand.Parameters.AddWithValue("phone", "%" + value + "%");
                selectCommand.Parameters.AddWithValue("name", "%" + value + "%");
                MySqlDataReader rs = selectCommand.ExecuteReader();
                ArrayList list = new ArrayList();
                while (rs.Read())
                {
                    cs = new Customer();
                    cs.customerId = rs.GetInt32("customerId");
                    cs.customerName = rs.GetString("customerName");
                    cs.serial = rs.GetString("serial");
                    cs.phone = rs.GetString("phone");
                    try
                    {
                        cs.email = rs.GetString("email");
                        cs.facebook = rs.GetString("facebook");
                    }
                    catch (System.Data.SqlTypes.SqlNullValueException se)
                    {
                    }
                    customerCache[cs.phone] = cs;
                    list.Add(cs);

                }
                rs.Close();
                selectCommand.Dispose();
                return list;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                DBConnection.releaseConnection(conn);
            }

            return null;

        }
    }
}
